<?php

include("../config/db_config.php");
include("../vendor/php-pdo/class.db.php");

$accord_db = new db("mysql:host=127.0.0.1;port=3306;dbname=accord", "root", "");

$accord_db->run('DROP TABLE IF EXISTS `product_price`;');
$accord_db->run('DROP TABLE IF EXISTS `product_offers`;');
$accord_db->run('DROP TABLE IF EXISTS `product_serialize`;');
$accord_db->run('DROP TABLE IF EXISTS `product_cat_model_lkp`;');
$accord_db->run('DROP TABLE IF EXISTS `products`;');
$accord_db->run('DROP TABLE IF EXISTS `categories`;');
$accord_db->run('DROP TABLE IF EXISTS `models`;');
////////////////////////////////////////////////////////////////////////////////
//								categories									  //
////////////////////////////////////////////////////////////////////////////////
$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `main` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `categories` ADD PRIMARY KEY (`id`);
ALTER TABLE `categories` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `categories` ADD `active` TINYINT NOT NULL DEFAULT '1' ;
MYSQL;
$accord_db->run($sql);

$cats = array(
  array("Batteries & Accessories","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15719911"),
  array("Bearings & Seals","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15720041"),
  array("Belts, Hoses & Pulleys","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15721321"),
  array("Brake System","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15721631"),
  array("Cables","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15722931"),
  array("Caps","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15723031"),
  array("Engine Cooling & Climate Control","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:2286886011"),
  array("Engines & Engine Parts","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15725501"),
  array("Exhaust & Emissions","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:2286887011"),
  array("Filters","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15727061"),
  array("Fuel System","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15727321"),
  array("Gaskets","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15728301"),
  array("Ignition Parts","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15728931"),
  array("Lighting & Electrical","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15729591"),
  array("Motors","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15730381"),
  array("Sensors","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15730551"),
  array("Shocks, Struts & Suspension","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:2286889011"),
  array("Starters & Alternators","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:193805011"),
  array("Steering System","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15731431"),
  array("Switches & Relays","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15732461"),
  array("Transmission & Drive Train","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15723911"),
  array("Windshield Wipers & Washers","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:15734781"),
  array("Window Regulators & Motors","Replacement Parts","rh=n:15684181,n:!15690151,n:15719731,n:3095176011"),
  array("Body Armor","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:4939604011"),
  array("Bumpers & Bumper Accessories","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735201"),
  array("Cargo Management","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735241"),
  array("Chrome Trim & Accessories","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:404963011"),
  array("Covers","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15857521"),
  array("Decals & Bumper Stickers","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15710001"),
  array("Deflectors & Shields","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735601"),
  array("Emblems","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:3095127011"),
  array("Fender Flares & Trim","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735691"),
  array("Gas Caps","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735781"),
  array("Grilles & Grille Guards","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15735981"),
  array("Hood Scoops & Vents","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15736031"),
  array("Horns & Accessories","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15736071"),
  array("License Plate Covers & Frames","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15736161"),
  array("Mirrors","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15857531"),
  array("Mud Flaps & Splash Guards","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15706651"),
  array("Safety","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15857541"),
  array("Snow & Ice","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15737021"),
  array("Spoilers, Wings & Styling Kits","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15737061"),
  array("Towing Products & Winches","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15737251"),
  array("Trailer Accessories","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15737621"),
  array("Truck Bed & Tailgate Accessories","Exterior Accessories","rh=n:15684181,n:!15690151,n:15857511,n:15737781"),
  array("Antitheft","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15735141"),
  array("Ashtrays","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:318293011"),
  array("Automobilia","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15735191"),
  array("Covers","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15735551"),
  array("Door Entry Guard","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:3097820011"),
  array("Floor Mats & Cargo Liners","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15735731"),
  array("Gauges","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15729701"),
  array("Insulation & Noise Control","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15736121"),
  array("Pedals & Pedal Accessories","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15736571"),
  array("Safety","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15857551"),
  array("Seat Covers & Accessories","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15736751"),
  array("Shift Boots & Knobs","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15736991"),
  array("Steering Wheels & Accessories","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15737141"),
  array("Sun Protection","Interior Accessories","rh=n:15684181,n:!15690151,n:15857501,n:15737191"),
  array("Tires","Tires & Wheels","rh=n:15684181,n:!15690151,n:15706571,n:353609011"),
  array("Wheels","Tires & Wheels","rh=n:15684181,n:!15690151,n:15706571,n:353596011"),
  array("Accessories & Parts","Tires & Wheels","rh=n:15684181,n:!15690151,n:15706571,n:15706581"),
  array("Body","Body Parts & Trim","rh=n:15684181,n:!15690151,n:15709231,n:15709241"),
  array("Paints & Primers","Body Parts & Trim","rh=n:15684181,n:!15690151,n:15709231,n:15709851"),
  array("Trim","Body Parts & Trim","rh=n:15684181,n:!15690151,n:15709231,n:15709991"),
);
foreach ($cats as $cat) {
    $insert = array(
        "name" => $cat[0], "main" => $cat[1], "path" => $cat[2]
    );
    $accord_db->insert("categories", $insert);
}
////////////////////////////////////////////////////////////////////////////////
//									models									  //
////////////////////////////////////////////////////////////////////////////////
$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `models` (
`id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `att1` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `att2` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `att3` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `models` ADD PRIMARY KEY (`id`);
ALTER TABLE `models` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `models` ADD INDEX(`path`);
MYSQL;
$accord_db->run($sql);
// 2011
$models_list = array(
    array('2011', 'EX', '2.4 L4', 'Sedan', '2011-59-751-191--1-6-5-8981--1-1---4-1'),
    array('2011', 'EX', '2.4 L4', 'Coupe', '2011-59-751-191--7-6-5-8981--1-1---4-1'),
    array('2011', 'EX', '3.5 V6', 'Sedan', '2011-59-751-191--1-6-5-8980--1-1---4-1'),
    array('2011', 'EX', '3.5 V6', 'Coupe', '2011-59-751-191--7-6-5-8980--1-1---4-1'),
    array('2011', 'EX-L', '2.4 L4', 'Sedan', '2011-59-751-2117--1-6-5-8981--1-1---4-1'),
    array('2011', 'EX-L', '2.4 L4', 'Coupe', '2011-59-751-2117--7-6-5-8981--1-1---4-1'),
    array('2011', 'EX-L', '3.5 V6', 'Sedan', '2011-59-751-2117--1-6-5-8980--1-1---4-1'),
    array('2011', 'EX-L', '3.5 V6', 'Coupe', '2011-59-751-2117--7-6-5-8980--1-1---4-1'),
    array('2011', 'LX', '2.4 L4', 'Sedan', '2011-59-751-113--1-6-5-8981-2588-1-1---4-1'),
    array('2011', 'LX-P', '2.4 L4', 'Sedan', '2011-59-751-2198--1---8982------4-1'),
    array('2011', 'LX-S', '2.4 L4', 'Coupe', '2011-59-751-2199--7---8981------4-1'),
    array('2011', 'SE', '2.4 L4', 'Sedan', '2011-59-751-49--1---8982------4-1'),
    array('2012', 'EX', '2.4 L4', 'Sedan', '2012-59-751-191--1-6-5-8981--1-1---3-1'),
    array('2012', 'EX', '2.4 L4', 'Coupe', '2012-59-751-191--7-6-5-8981--1-1---3-1'),
    array('2012', 'EX', '3.5 V6', 'Sedan', '2012-59-751-191--1-6-5-8980--1-1---3-1'),
    array('2012', 'EX', '3.5 V6', 'Coupe', '2012-59-751-191--7-6-5-8980--1-1---3-1'),
    array('2012', 'EX-L', '2.4 L4', 'Sedan', '2012-59-751-2117--1-6-5-8981--1-1---3-1'),
    array('2012', 'EX-L', '2.4 L4', 'Coupe', '2012-59-751-2117--7-6-5-8981--1-1---3-1'),
    array('2012', 'EX-L', '3.5 V6', 'Sedan', '2012-59-751-2117--1-6-5-8980--1-1---3-1'),
    array('2012', 'EX-L', '3.5 V6', 'Coupe', '2012-59-751-2117--7-6-5-8980--1-1---3-1'),
    array('2012', 'LX', '2.4 L4', 'Sedan', '2012-59-751-113--1---8982------3-1'),
    array('2012', 'LX-P', '2.4 L4', 'Sedan', '2012-59-751-2198--1---8982------3-1'),
    array('2012', 'LX-S', '2.4 L4', 'Coupe', '2012-59-751-2199--7---8981------3-1'),
    array('2012', 'SE', '2.4 L4', 'Sedan', '2012-59-751-49--1---8982------3-1'),
    array('2013', 'EX', '2.4 L4', 'Sedan', '2013-59-751-191--1-6-5-18251--1-1---2-1'),
    array('2013', 'EX', '2.4 L4', 'Coupe', '2013-59-751-191--7-6-5-18251--1-1---2-1'),
    array('2013', 'EX-L', '2.4 L4', 'Sedan', '2013-59-751-2117--1-6-5-18251--1-1---2-1'),
    array('2013', 'EX-L', '2.4 L4', 'Coupe', '2013-59-751-2117--7-6-5-18251--1-1---2-1'),
    array('2013', 'EX-L', '3.5 V6', 'Sedan', '2013-59-751-2117--1-6-5-18249--1-1---2-1'),
    array('2013', 'EX-L', '3.5 V6', 'Coupe', '2013-59-751-2117--7-6-5-18249--1-1---2-1'),
    array('2013', 'LX', '2.4 L4', 'Sedan', '2013-59-751-113--1---18251------2-1'),
    array('2013', 'LX-S', '2.4 L4', 'Coupe', '2013-59-751-2199--7---18251------2-1'),
    array('2013', 'Sport', '2.4 L4', 'Sedan', '2013-59-751-141--1---18250------2-1'),
    array('2013', 'Touring', '3.5 V6', 'Sedan', '2013-59-751-53--1---18249------2-1'),
);
foreach ($models_list as $model) {
    $insert = array(
        "year" => $model[0], "att1" => $model[1], "att2" => $model[2], "att3" => $model[3], "path" => $model[4]
    );
    $accord_db->insert("models", $insert);
}
////////////////////////////////////////////////////////////////////////////////
//									products								  //
////////////////////////////////////////////////////////////////////////////////
$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NULL,
  `asin` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `reviewers` int(11) NULL DEFAULT 0,
  `saved` int(11) NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `products` ADD `update_id` VARCHAR(50) NULL;
ALTER TABLE `products` ADD `created_time` INT NULL ;
ALTER TABLE `products` ADD `updated_time` INT NULL ;
ALTER TABLE `products` ADD `valid` TINYINT NOT NULL DEFAULT 0 ;
ALTER TABLE `products` CHANGE `update_id` `update_id` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '---';
ALTER TABLE `products` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `asin` (`asin`);
ALTER TABLE `products` ADD KEY (`url`);
ALTER TABLE `products` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
MYSQL;
$accord_db->run($sql);

$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `product_cat_model_lkp` (
  `product_asin` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL,
  `model_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `product_cat_model_lkp`
 ADD UNIQUE KEY `unique` (`product_asin`,`cat_id`,`model_id`), ADD KEY `cat_id` (`cat_id`), ADD KEY `model_id` (`model_id`), ADD KEY `product_asin` (`product_asin`);

ALTER TABLE `product_cat_model_lkp`
ADD CONSTRAINT `product_cat_model_lkp_ibfk_2` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`id`),
ADD CONSTRAINT `product_cat_model_lkp_ibfk_3` FOREIGN KEY (`model_id`) REFERENCES `models` (`id`),
ADD CONSTRAINT `product_cat_model_lkp_ibfk_4` FOREIGN KEY (`product_asin`) REFERENCES `products` (`asin`);
MYSQL;
$accord_db->run($sql);

$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `product_serialize` (
  `product_asin` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `serialize` longtext COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `product_serialize` ADD KEY `product_asin` (`product_asin`);
ALTER TABLE `product_serialize` ADD CONSTRAINT `product_serialize_ibfk_1` FOREIGN KEY (`product_asin`) REFERENCES `products` (`asin`);
MYSQL;
$accord_db->run($sql);

$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `product_offers` (
  `product_asin` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `condition` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `seller_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `seller_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `seller_img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` int(11) NOT NULL,
  `price_formatted` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipping` varchar(255) COLLATE utf8_unicode_ci DEFAULT '0',
  `ship_from` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `product_offers` ADD KEY `product_asin` (`product_asin`);
ALTER TABLE `product_offers` ADD CONSTRAINT `product_offers_ibfk_1` FOREIGN KEY (`product_asin`) REFERENCES `products` (`asin`);
MYSQL;
$accord_db->run($sql);

$sql = <<<MYSQL
CREATE TABLE IF NOT EXISTS `product_price` (
  `product_asin` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lowest_price` int(11) NOT NULL,
  `lowest_price_formatted` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lowest_price_when` int(11) NOT NULL,
  `current_price` int(11) NOT NULL,
  `current_price_formatted` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `current_price_when` int(11) NOT NULL,
  `highest_price` int(11) NOT NULL,
  `highest_price_formatted` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `highest_price_when` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `product_price` ADD KEY (`product_asin`);
ALTER TABLE `product_price` ADD CONSTRAINT `product_price_ibfk_1` FOREIGN KEY (`product_asin`) REFERENCES `products` (`asin`);
MYSQL;
$accord_db->run($sql);